package com.hillinsight.service;

import com.hillinsight.entity.MySQLColumns;
import com.hillinsight.entity.MySQLTables;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by fangzheng on 2018/9/5.
 */
@Service
public class MySQLService {

    @Autowired
    @Qualifier("mysqlJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    private String getDbName(){
        String database_name = null;
        try {
            database_name = jdbcTemplate.getDataSource().getConnection().getCatalog();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return database_name;
    }

    /**
     * 所有数据库
     * @param sql
     * @return
     */
//    public List getAllDatabases(String sql){
//        List databases = new ArrayList();
//        List rows = jdbcTemplate.queryForList("SELECT TABLE_SCHEMA FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;");
//        Iterator it = rows.iterator();
//        while(it.hasNext()) {
//            Map userMap = (Map) it.next();
//            databases.add(userMap.get("TABLE_SCHEMA"));
//        }
//        return databases;
//    }

    /**
     * 当前连接的数据库
     * 也可以通过SELECT database();来获取当前连接的数据库名称
     * @param sql
     * @return
     */
    public List getAllDatabases(String sql){
        List databases = new ArrayList();
        List rows = jdbcTemplate.queryForList("SELECT TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA = '"+getDbName()+"' GROUP BY TABLE_SCHEMA;");
        Iterator it = rows.iterator();
        while(it.hasNext()) {
            Map userMap = (Map) it.next();
            databases.add(userMap.get("TABLE_SCHEMA"));
        }
        return databases;
    }

    public List<MySQLTables> getAllTables(){
        List<MySQLTables> databases = new ArrayList<MySQLTables>();
        String sql = "SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='"+getDbName()+"';";
        return jdbcTemplate.query(sql,new MySQLTableRowMapper());
    }

    public List<MySQLColumns> getAllColumns(String table_name){
        String sql = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='"+getDbName()+"' and TABLE_NAME='"+table_name+"';";
        return jdbcTemplate.query(sql,new MySQLColumnRowMapper());
    }

    class MySQLTableRowMapper implements RowMapper<MySQLTables> {
        //rs为返回结果集，以每行为单位封装着
        public MySQLTables mapRow(ResultSet rs, int rowNum) throws SQLException {
            MySQLTables table = new MySQLTables();
            table.setTable_catalog(rs.getString("Table_catalog"));
            table.setTable_schema(rs.getString("Table_schema"));
            table.setTable_name(rs.getString("Table_name"));
            table.setTable_type(rs.getString("Table_type"));
            table.setEngine(rs.getString("Engine"));
            table.setVersion(rs.getString("Version"));
            table.setRow_format(rs.getString("Row_format"));
            table.setTable_rows(rs.getString("Table_rows"));
            table.setAvg_row_length(rs.getString("Avg_row_length"));
            table.setData_length(rs.getString("Data_length"));
            table.setMax_data_length(rs.getString("Max_data_length"));
            table.setIndex_length(rs.getString("Index_length"));
            table.setData_free(rs.getString("Data_free"));
            table.setAuto_increment(rs.getString("Auto_increment"));
            table.setCreate_time(rs.getDate("Create_time"));
            table.setUpdate_time(rs.getDate("Update_time"));
            table.setCheck_time(rs.getDate("Check_time"));
            table.setTable_collation(rs.getString("Table_collation"));
            table.setChecksum(rs.getString("Checksum"));
            table.setCreate_options(rs.getString("Create_options"));
            table.setTable_comment(rs.getString("Table_comment"));
            return table;
        }
    }
    class MySQLColumnRowMapper implements RowMapper<MySQLColumns> {
        //rs为返回结果集，以每行为单位封装着
        public MySQLColumns mapRow(ResultSet rs, int rowNum) throws SQLException {
            MySQLColumns table = new MySQLColumns();
            table.setTABLE_CATALOG(rs.getString("TABLE_CATALOG"));
            table.setTABLE_SCHEMA(rs.getString("TABLE_SCHEMA"));
            table.setTABLE_NAME(rs.getString("TABLE_NAME"));
            table.setCOLUMN_NAME(rs.getString("COLUMN_NAME"));
            table.setORDINAL_POSITION(rs.getString("ORDINAL_POSITION"));
            table.setCOLUMN_DEFAULT(rs.getString("COLUMN_DEFAULT"));
            table.setIS_NULLABLE(rs.getString("IS_NULLABLE"));
            table.setDATA_TYPE(rs.getString("DATA_TYPE"));
            table.setCHARACTER_MAXIMUM_LENGTH(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
            table.setCHARACTER_OCTET_LENGTH(rs.getString("CHARACTER_OCTET_LENGTH"));
            table.setNUMERIC_PRECISION(rs.getString("NUMERIC_PRECISION"));
            table.setNUMERIC_SCALE(rs.getString("NUMERIC_SCALE"));
            table.setDATETIME_PRECISION(rs.getString("DATETIME_PRECISION"));
            table.setCHARACTER_SET_NAME(rs.getString("CHARACTER_SET_NAME"));
            table.setCOLLATION_NAME(rs.getString("COLLATION_NAME"));
            table.setCOLUMN_TYPE(rs.getString("COLUMN_TYPE"));
            table.setCOLUMN_KEY(rs.getString("COLUMN_KEY"));
            table.setEXTRA(rs.getString("EXTRA"));
            table.setPRIVILEGES(rs.getString("PRIVILEGES"));
            table.setCOLUMN_COMMENT(rs.getString("COLUMN_COMMENT"));
            table.setGENERATION_EXPRESSION(rs.getString("GENERATION_EXPRESSION"));
            return table;
        }
    }

}
